<?php


namespace App\Exports\SearchStage;

use App\Models\PartCompany;
use App\Services\BookCatalogService;
use App\Services\CommonService;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\RegistersEventListeners;
use Maatwebsite\Excel\Concerns\WithColumnWidths;
use Maatwebsite\Excel\Concerns\WithCustomStartCell;
use Maatwebsite\Excel\Concerns\WithStyles;
use Maatwebsite\Excel\Concerns\WithTitle;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Worksheet\PageMargins;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;


/**
 * Class BookCatalogExport29
 * @package App\Exports
 * 按照参建单位 29号文导出卷内
 *
 * 整体实现方法：
 * 第一步：  先把全部 卷内数据渲染到 excel ，中间记下需要插入 头部三行的row位置，和各空行插入点位置
 * 第二步:  插入空行
 * 第三步： 插入头部三行
 *
 */
class UpBookCatalogExport29 implements FromCollection, WithTitle, WithColumnWidths, WithStyles, WithCustomStartCell
{
    use Exportable,RegistersEventListeners;

    const HEADER_ONE_HEIGHT   = 66.95; //卷内目录
    const HEADER_TWO_HEIGHT   = 23.1;  //档案号
    const HEADER_THREE_HEIGHT = 56.25; //属性名
    const DATA_ROW_HEIGHT     = 42;    //数据行
    const REDIS_EXPIRE_TIME   = 1200;  //redis有效期

    protected $partCompanyId;     //参见单位
    protected $projectClassifyId; //单位工程
    protected $selectRows;        //用户选择的案卷
    protected $column;            //总行数
    protected $childScope;        //卷序号范围
    protected $headerIndexKey;    //头部三行序号起点
    protected $headerIndexTmp;    //暂存头部三行位置
    protected $redisKey;          //随机字符串

    public function __construct($partCompanyId, $projectClassifyId, $ids, $serialNos)
    {
        $this->redisKey          = get_rand_str('export_catalog_tmp'); //创造个临时存储redis，用完销毁
        $this->partCompanyId     = $partCompanyId;
        $this->projectClassifyId = $projectClassifyId;
        $this->childScope        = $serialNos;
        $this->bookTitleIds      = to_array($ids);
        $this->headerIndexKey    = 'A_search_stage_header_index:' . $this->redisKey;
        $this->headerIndexTmp    = 'A_search_stage_header_index_tmp:' . $this->redisKey;
        $this->firstHeaderKey    = 'A_search_stage_first:' . $this->redisKey; //第一个案卷
        $this->tmp15             = 'A_search_stage_tmp15:' . $this->redisKey; //用来存一个临时数据，excel每增加15行
    }

    public function collection()
    {
        $suffix = $this->partCompanyId . '_' . $this->projectClassifyId;
        $list = DB::table("book_titles_{$suffix}")->from("book_titles_{$suffix} as bt")
            ->leftJoin("book_catalogs_{$suffix} as bc", 'bt.id', '=', 'bc.book_title_id')
            ->select('bt.id', 'bt.archives_no', 'bc.book_title_id', 'bc.serial_no', 'bc.file_number', 'bc.director', 'bc.title', 'bc.c_date', 'bc.page_num', 'bc.remark')
            ->whereIn('bt.id', $this->bookTitleIds)
            ->whereNull('bt.deleted_at')
            ->whereNull('bc.deleted_at')
            ->orderBy('bt.serial_no')
            ->get()
            ->groupBy('book_title_id');

        //初始化插入的第一个 头部三行 插入点
        redis()->lpush($this->headerIndexKey, 1);
        redis()->set($this->headerIndexTmp, 1);
        redis()->set($this->firstHeaderKey, 1); //设置个临时变量，只用于第一个头部写入判断

        $resultList = [];
        $tmpKey = $this->tmp15;
        redis()->set($tmpKey, 0); //设置个临时变量，记录达到15条数据，就插入三行头

        foreach ($list as $val) { //遍历获得每一个案卷的卷内
            $catalogs = json_decode($val, true);
            $bookTitleId = $catalogs[0]['book_title_id'];
            if (empty($bookTitleId)) {
                continue;  //如果某个案卷为空，略过
            };
            $catalogCount = count($catalogs); //卷内数量
            $pageNum      = ceil($catalogCount / 15); //要分成几页（向上取整）
            $remainder    = $catalogCount % 15; //余数
            $emptyRows    = 15 - $remainder; //最后一页卷内需要添加多少空行
            for ($i = 1; $i <= $pageNum; $i++) { //有几页，就把档案号加进去几次
                redis()->incrby($this->headerIndexTmp, 18);
                $headerRows = redis()->get($this->headerIndexTmp);
                redis()->lpush($this->headerIndexKey, $headerRows); //头部三行插入点存进去
            }
            $maxSerialNo = max(array_column($catalogs, 'serial_no')); //获取这个案卷里面的最大序号
            foreach ($catalogs as $val2) {
                //增加头部三行
                if (redis()->get($this->firstHeaderKey) == 1) { //这里只会执行一次
                    $resultList = $this->addHeader($resultList, $val2['archives_no']);
                    redis()->incrby($this->firstHeaderKey, 1);
                }
                if (redis()->get($tmpKey) == 15) {
                    $resultList = $this->addHeader($resultList, $val2['archives_no']);
                    redis()->set($tmpKey, 0); //重置为0
                }
                //增加卷内数据
                $arr['number']      = $val2['serial_no'];
                $arr['file_number'] = format_str($val2['file_number']);
                $arr['director']    = format_str($val2['director']);
                $arr['title']       = format_str($val2['title']);
                $arr['c_date']      = $val2['c_date'];
                $arr['page_num']    = $val2['page_num'];
                $arr['remark']      = format_str($val2['remark']);
                $resultList[]       = $arr;
                if ($val2['serial_no'] && is_numeric($val2['serial_no'])) {
                    redis()->incrby($tmpKey, 1);
                }
                //增加空行
                if ($maxSerialNo == $val2['serial_no']) { //最后一行
                    if ($remainder) { //有余数
                        $resultList = $this->addEmptyRows($resultList, $emptyRows);
                        redis()->incrby($tmpKey, $emptyRows);
                    }
                }
            }
        }
        //用完清除
        redis()->del($this->tmp15);
        redis()->del($this->firstHeaderKey);
        redis()->expire($this->headerIndexKey, self::REDIS_EXPIRE_TIME);
        redis()->expire($this->headerIndexTmp, self::REDIS_EXPIRE_TIME);
        $this->column = count($resultList);

        return collect($resultList);
    }

    /**
     * 添加表头
     * @author: lxk
     * @date: 2023/7/12 17:37
     */
    public function addHeader($resultList, $archivesNo)
    {
        $header1['number']      = '卷内目录';
        $header1['file_number'] = '';
        $header1['director']    = '';
        $header1['title']       = '';
        $header1['c_date']      = '';
        $header1['page_num']    = '';
        $header1['remark']      = '';

        $resultList[] = $header1;

        $header2['number']      = '档案号：' . $archivesNo;
        $header2['file_number'] = '';
        $header2['director']    = '';
        $header2['title']       = '';
        $header2['c_date']      = '';
        $header2['page_num']    = '';
        $header2['remark']      = '';

        $resultList[] = $header2;

        $header3['number']      = '序号';
        $header3['file_number'] = '文件
编号';
        $header3['director']    = '责任者';
        $header3['title']       = '文件题名';
        $header3['c_date']      = '日
期';
        $header3['page_num']    = '页
号';
        $header3['remark']      = '备
注';
        $resultList[] = $header3;

        return $resultList;
    }

    /**
     * 增加几个空行
     */
    public function addEmptyRows($resultList, $num)
    {
        for ($i = 0; $i < $num; $i++) {
            $empty['number']      = ''; //这里必须给个空格，如果为空字符串的话，有时候会产生bug！
            $empty['file_number'] = '';
            $empty['director']    = '';
            $empty['title']       = '';
            $empty['c_date']      = '';
            $empty['page_num']    = '';
            $empty['remark']      = '';

            $resultList[] = $empty;
        }
        return $resultList;
    }


    /**
     * 样式设置
     */
    public function styles(Worksheet $sheet)
    {
        /**
         * 设置为 A4纸
         */
        $pageSetup = new PageSetup();
        $pageSetup->setPaperSize(9);
        $sheet->setPageSetup($pageSetup);
        /**
         * 设置页边距
         */
        $pageMargins = new PageMargins();
        $pageMargins->setTop(0.5);
        $pageMargins->setRight(0.6);
        $pageMargins->setLeft(0.8);
        $sheet->setPageMargins($pageMargins);
        /**
         * 设置字体和样式
         */
        $sheet->getStyle('A1:G' . $this->column)->applyFromArray([
            'font' => [
                'name' => '宋体',
            ],
            'alignment' => [
                'vertical' => 'center',
                'wrapText' => true, //自动换行
            ],
        ]);
        $cells       = []; //边框范围
        $headerCount = redis()->llen($this->headerIndexKey);
        for ($i = 0; $i < $headerCount - 1; $i++) {
            $headerStartIndex = redis()->rpop($this->headerIndexKey);
            $this->setHeaderStyle($sheet, $headerStartIndex);
            $start   = $headerStartIndex + 2;
            $end     = $start + 15;
            $cells[] = "A{$start}:G{$end}";
        }
        /**
         * 设置边框
         */
        foreach ($cells as $cell) { //设置边框范围 （循环次数和页数相等）
            $styleArray = [
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => Border::BORDER_THIN //细边框
                    ]
                ]
            ];
            $sheet->getStyle($cell)->applyFromArray($styleArray);
        }
    }

    /**
     * 设置三行表头样式
     */
    private function setHeaderStyle(Worksheet $sheet, $newRow)
    {
        $one   = $newRow;
        $two   = $newRow + 1;
        $three = $newRow + 2;
        $four  = $newRow + 3;

        //先全部水平居中
        $sheet->getStyle('A' . $one . ':G' . $this->column)->getAlignment()->setHorizontal('center'); //居中对齐

        //卷内目录
        $sheet->mergeCells('A' . $one . ':G' . $one);
        $sheet->getStyle('A' . $one)->getFont()->setSize(22);
        $sheet->getStyle('A' . $one)->getFont()->setBold(true);

        //档案号
        $sheet->mergeCells('A' . $two . ':G' . $two);
        $sheet->getStyle('A' . $two)->getAlignment()->setHorizontal('right');

        //数据行
        $sheet->getStyle('A' . $two . ':G' . ($two + 16))->getFont()->setSize(10); //字体大小
        $sheet->getStyle('B' . $four . ':C' . ($four + 15))->getFont()->setSize(9);//文件编号、责任者
        $sheet->getStyle('D' . $four . ':D' . ($four + 15))->getAlignment()->setHorizontal('left');

        //属性名
        $sheet->getStyle('A' . $three . ':G' . $three)->getFont()->setSize(12);
        $sheet->getStyle('A' . $three . ':G' . $three)->getFont()->setBold(true);

        $this->setRowHeight($sheet, $one);
    }

    /**
     * 设置行高
     */
    private function setRowHeight($sheet, $one)
    {
        $sheet->getRowDimension($one)->setRowHeight(self::HEADER_ONE_HEIGHT);
        $sheet->getRowDimension($one + 1)->setRowHeight(self::HEADER_TWO_HEIGHT);
        $sheet->getRowDimension($one + 2)->setRowHeight(self::HEADER_THREE_HEIGHT);
        for ($row = 3; $row < 18; $row++) {
            $sheet->getRowDimension($one + $row)->setRowHeight(self::DATA_ROW_HEIGHT);
        }
    }

    /**
     * sheet名称
     * @return string
     */
    public function title(): string
    {
        return $this->childScope;
    }

    /**
     * 设置列宽
     * @return array
     */
    public function columnWidths(): array
    {
        return [
            'A' => 4.5, 'B' => 9, 'C' => 11, 'D' => 38.5, 'E' => 9.7, 'F' => 7.3, 'G' => 7
        ];
    }

    /**
     * 从哪个单元格开始渲染数据，数据集合如 $list
     * @return string
     */
    public function startCell(): string
    {
        return 'A1';
    }

}
